package com.ran.dao.daoImpl;

import com.ran.dao.empDao;
import com.ran.entity.employee;
import com.ran.utils.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *empDao接口实现类
 */
public class empDaoImpl implements empDao {
    @Override
//    登陆验证
    public boolean login(String ename, String epsd) throws SQLException {
//        分别定义数据库连接对象,sql语句,预编译执行对象,结果集对象
        Connection connect = jdbc.getconnect();
        String sql = "SELECT * FROM tblemp WHERE ename= ? AND epsd= ?";
        PreparedStatement statement=null;
        ResultSet result=null;
        boolean flag=false;
        //执行sql语句,得到DQL结果
        try {
//            预编译
            statement = connect.prepareStatement(sql);
//            设值
            statement.setString(1,ename);
            statement.setString(2,epsd);
//            执行sql语句
            result=statement.executeQuery();
//        处理DQL结果,如果查到了此人信息,则返回true
            if(result.next()){
                flag=true;
//                System.out.println("查询成功!");
            }
        } catch (SQLException e) {
            System.out.println("sql语句执行出错!");
            e.printStackTrace();
        }finally {
            jdbc.realse(connect,statement,result);
        }
//        System.out.println(flag);
        return flag;
    }

//获取所有员工信息
    @Override
    public List<employee> allemp() {
//        准备工作
        Connection connection = jdbc.getconnect();
        String sql = "SELECT * from tblemp";
        String sql2 = "SELECT dname FROM tbldept WHERE deptid = ?";
        PreparedStatement statement1 =null;
        PreparedStatement statement2 =null;
        ResultSet result =null;
        ResultSet result2 =null;
        ArrayList<employee> list =new ArrayList<>();
//        操作数据库,执行sql语句
        try {
            statement1 = connection.prepareStatement(sql);
            statement2 = connection.prepareStatement(sql2);
            result = statement1.executeQuery();
//            循环取出数据并存入list
            while(result.next()){
                int empid = result.getInt(1);
                String ename = result.getString(2);
                int depid = result.getInt("depid");
//                在sql的结果内部执行sql2,通过result的depid获取tbldept表的dname字段
                statement2.setInt(1,depid);
//                System.out.println("depid:"+depid);
                result2 = statement2.executeQuery();
//                获取sql2的执行结果
                if(result2.next()){
                    String dname = result2.getString(1);
//                    System.out.println("dname:"+dname);
//              若dname存在,则将值以employee对象存入list中,否则不作存储
                    list.add(new employee(empid, ename, dname));
//                    employee em = new employee(empid, ename, dname);
//                    System.out.println("em.dname:"+em.getDname());
//                    list.add(em);
                }
            }
        } catch (SQLException e) {
            System.out.println("sql执行出错!");
            e.printStackTrace();
        } finally {
            jdbc.realse(connection,statement2,statement1,result);
        }
//        System.out.println(list);
        return list;
    }

//添加员工信息

    @Override
    public Integer register(employee em) {
//        数据准备
        Connection connection = jdbc.getconnect();
        String sql = "INSERT INTO tblemp VALUES(null,?,?,?,null);";
        PreparedStatement statement = null;
        Integer result = null;
//        操作数据库,执行sql语句,获得受影响行数
        try {
//            预编译sql
            statement = connection.prepareStatement(sql);
//            为sql赋值
            statement.setString(1,em.getEname());
            statement.setInt(2,em.getEgendar());
            statement.setInt(3,em.getDepid());
//            执行sql语句
            result = statement.executeUpdate();
        } catch (SQLException e) {
            System.out.println("添加员工信息出错!");
            e.printStackTrace();
        } finally {
            jdbc.realse(connection,statement,null);
        }
        return result;
    }
//    根据员工id查看一个员工的信息
    @Override
    public List<employee> search(int empid) {
//        准备数据
        Connection connect = jdbc.getconnect();
        String sql = "SELECT * FROM tblemp WHERE empid= ?";
        PreparedStatement statement=null;
        ResultSet result;
        ArrayList<employee> searchlist = new ArrayList<>();
//        操作数据库,执行sql语句
        try {
            statement=connect.prepareStatement(sql);
            statement.setInt(1,empid);
            result= statement.executeQuery();
//            处理数据
            if(result.next()){
                String enamese = result.getString("ename");
                int egendarse= result.getInt("egendar");
                int depidse = result.getInt("depid");
                searchlist.add(new employee(enamese, egendarse, depidse));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            jdbc.realse(connect,statement,null);
        }
//        System.out.println("searchdao的查询结果："+searchlist);
//        System.out.println("dao层的searchlist："+searchlist.get(0).toStringsearch());
        return searchlist;
    }
//      修改员工信息
    @Override
    public Integer update(employee emp) {
//        准备数据
        Connection connection = jdbc.getconnect();
        PreparedStatement statement = null;
        String sql = "UPDATE tblemp SET ename=?,egendar=?,depid=? WHERE empid=?";
        Integer result = null;
//        操作数据库，执行sql语句
        try {
            statement=connection.prepareStatement(sql);
            statement.setString(1,emp.getEname());
            statement.setInt(2,emp.getEgendar());
            statement.setInt(3,emp.getDepid());
            statement.setInt(4,emp.getEmpid());
            result= statement.executeUpdate();
        } catch (SQLException e) {
            System.out.println("员工修改sql语句执行出错--dao");
            e.printStackTrace();
        } finally {
            jdbc.realse(connection,statement,null);
        }
        return result;
    }
//    删除员工信息

    @Override
    public Integer delete(int empid) {
//       准备数据
        Connection connect = jdbc.getconnect();
        String sql = "DELETE FROM tblemp WHERE empid=?";
        PreparedStatement statement=null;
        Integer result = null;
//        操作数据库，执行sql语句
        try {
            statement=connect.prepareStatement(sql);
            statement.setInt(1,empid);
            result=statement.executeUpdate();
        } catch (SQLException e) {
            System.out.println("员工删除sql语句执行出错--dao");
            e.printStackTrace();
        } finally {
            jdbc.realse(connect,statement,null);
        }
        return result;
    }
}
